package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import jdbc.ConnectionFactory;
import Modelo.Cliente;

public class ClienteCnpjPostgresDAO implements ClienteCnpjDAO {
	//
	// ATRIBUTOS
	//
	private Connection cnn;
	private String sql;
	private PreparedStatement stmt;
	private ResultSet rs;

	//
	// CONTRUTOR
	//
	public ClienteCnpjPostgresDAO() {
		conectar();
	}

	//
	// INSERT
	//
	public boolean adicionar(Cliente c) {
		this.sql = "INSERT INTO slv_web.tbClienteCnpj (nome, cnpj, telefone, email,"
				+ "numHab, numReg, estadoEmi, validadeHab) VALUES (?,?,?,?,?,?,?,?)";
		conectar();

		try {
			stmt = cnn.prepareStatement(sql);

			stmt.setString(1, c.getNome());
			stmt.setString(2, c.getCnpj());
			stmt.setString(3, c.getTelefone());
			stmt.setString(4, c.getEmail());
			stmt.setString(5, c.getNumHab());
			stmt.setString(6, c.getNumReg());
			stmt.setString(7, c.getEstadoEmi());
			stmt.setString(8, c.getValidadeHab());

			stmt.execute();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			return false;
		} finally {
			fechar();
		}
	}

	//
	// PESQUISAS
	//
	public Cliente pesquisar(Cliente c) throws SQLException {
		this.sql = "SELECT * FROM sslv_web.tbClienteCnpj WHERE cnpj=?";
		Cliente cliente = new Cliente();
		conectar();

		try {
			stmt = cnn.prepareStatement(sql);
			stmt.setString(1, c.getCnpj());
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				cliente.setNome(rs.getString("nome"));
				cliente.setCnpj(rs.getString("cnpj"));
				cliente.setTelefone(rs.getString("telefone"));
				cliente.setEmail(rs.getString("email"));
				cliente.setNumHab(rs.getString("numHab"));
				cliente.setNumReg(rs.getString("numReg"));
				cliente.setEstadoEmi(rs.getString("estadoEmi"));
				cliente.setValidadeHab(rs.getString("validadeHab"));
			}
			rs.close();
			return cliente;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
		} finally {
			fechar();
		}
		return null;
	}
	
	public boolean validarCNPJ(String c) throws SQLException {
		this.sql = "SELECT * FROM slv_web.tbClienteCnpj WHERE cnpj=?";
		conectar();

		try {
			stmt = cnn.prepareStatement(sql);
			stmt.setString(1, c);
			rs = stmt.executeQuery();
			if (rs.next())
				return true;
			else
				return false;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			return false;
		} finally {
			fechar();
		} 
	}

	//
	// UPDATE
	//
	public boolean altera(Cliente c) {
		this.sql = "UPDATE slv_web.tbClienteCnpj SET nome=?, cnpj=?, telefone=?, email=?,"
				+ "numHab=?, numReg=?, estadoEmi=?, validadeHab=? where cnpj=?";
		conectar();

		try {
			PreparedStatement stmt = cnn.prepareStatement(sql);

			stmt.setString(9, c.getCnpj());
			stmt.setString(1, c.getNome());
			stmt.setString(2, c.getCnpj());
			stmt.setString(3, c.getTelefone());
			stmt.setString(4, c.getEmail());
			stmt.setString(5, c.getNumHab());
			stmt.setString(6, c.getNumReg());
			stmt.setString(7, c.getEstadoEmi());
			stmt.setString(8, c.getValidadeHab());

			stmt.execute();
			stmt.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			return false;
		} finally {
			fechar();
		}
	}

	//
	// DELETE
	//
	public boolean remover(Cliente c) {
		this.sql = "DELETE FROM slv_web.tbClienteCnpj WHERE cnpj=?";
		conectar();
		
		try {
			stmt = cnn.prepareStatement(sql);
			stmt.setString(1, c.getCnpj());
			stmt.execute();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			} 
		return false;
		}finally {
				fechar();
			}
		}

	//
	// CONECTAR
	//
	private void conectar() {
		this.cnn = new ConnectionFactory().getConnection();
	}

	//
	// FECHAR
	//
	private void fechar() {
		try {
			stmt.close();
			cnn.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
